package DataBase;


import java.sql.*;

public class World {

    public static void main(String[] args) {
        try
        {
            Connection conn = null;
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost/" +
                    "test?user=localhost&password=");

            if (conn != null)
            {
                System.out.println("We have connected to our database!");
                conn.close();
            }

            Connection con;
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/world?" +
                    "useUnicode=true&characterEncoding=UTF8","root", "");
            Statement statement = con.createStatement();

            String sql = "SELECT * FROM city WHERE city.Population>8017231\n";
            ResultSet rs = statement.executeQuery(sql);
            String name = null;
            String population = null;
            while(rs.next()){
                name = rs.getString("Name");
                population = rs.getString("Population");
                System.out.println(name + "\t" + population);
            }
            System.out.println();

            sql = "SELECT * FROM country WHERE country.LifeExpectancy=(SELECT MAX(country.LifeExpectancy)FROM country) \n";
            rs = statement.executeQuery(sql);
            String lifeExpectancy = null;
            while(rs.next()){
                name = rs.getString("Name");
                lifeExpectancy = rs.getString("LifeExpectancy");
                System.out.println(name + "\t" + lifeExpectancy);
            }
            System.out.println();

            sql = "SELECT * FROM country WHERE country.LifeExpectancy=(SELECT MIN(country.LifeExpectancy)FROM country) \n";
            rs = statement.executeQuery(sql);
            while(rs.next()){
                name = rs.getString("Name");
                lifeExpectancy = rs.getString("LifeExpectancy");
                System.out.println(name + "\t" + lifeExpectancy);
            }
            System.out.println();

            sql = "SELECT SUM(Population) FROM country WHERE country.Region=\"Middle East\"";
            rs = statement.executeQuery(sql);
            String sum = null;
            while(rs.next()){
                sum = rs.getString("SUM(Population)");
                System.out.println("SUM(Population)" + sum);
            }

        } catch (SQLException ex) {
            System.out.println("SQLException: " + ex.getMessage());
            ex.printStackTrace();
        } catch (Exception ex) {
            System.out.println("Exception: " + ex.getMessage());
            ex.printStackTrace();
        }
    }

}